﻿-- Script n°007: "mark" table.

BEGIN;

-- Drop objects if any
DROP TABLE IF EXISTS mark;
DROP SEQUENCE IF EXISTS seq_mark;

-- Create sequence
CREATE SEQUENCE seq_mark
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE seq_mark OWNER TO chelioudbdev;
GRANT ALL ON TABLE seq_mark TO chelioudbdev;
GRANT SELECT, UPDATE ON TABLE seq_mark TO cheliouwebdev;

-- Create table
CREATE TABLE mark (
   id                   NUMERIC              NOT NULL DEFAULT nextval('seq_mark'::regclass),
   date                 TIMESTAMP            NOT NULL,
   rank                 NUMERIC(1)           NOT NULL,
   photoid             NUMERIC              NOT NULL,
   memberid            NUMERIC              NOT NULL,
   CONSTRAINT pk_mark PRIMARY KEY (id),
   CONSTRAINT ak_unique_photo_mark UNIQUE (photoid, memberid)
)WITH (
  OIDS=FALSE
);

ALTER TABLE mark
   ADD CONSTRAINT fk_mark_member FOREIGN KEY (memberid)
      REFERENCES member (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE mark
   ADD CONSTRAINT fk_mark_photo FOREIGN KEY (photoid)
      REFERENCES photo (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;
      
ALTER TABLE mark OWNER TO chelioudbdev;
GRANT ALL ON TABLE mark TO chelioudbdev;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE mark TO cheliouwebdev;

COMMIT;
